﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text.RegularExpressions;
using System.Data;
using System.Text;
using System.Collections;
using System.Data.SqlClient;
using FrameWork;

namespace Slja.WEB.Finance
{
    public partial class DataInput : System.Web.UI.Page
    {
        BLL.ImportData bll = new BLL.ImportData();
        public string TableType
        {
            get { return Request.QueryString["type"]; }
        }
        public string TableName
        {
            get { return "f_" + TableType;}
        }

       
        protected void Page_Load(object sender, EventArgs e)
        {
            string str = Request.Form["chx"];
            tbxShow.Text = str;
            btnSave.ServerClick += new EventHandler(btnSave_ServerClick);
            myGridView.RowDataBound += new GridViewRowEventHandler(myGridView_RowDataBound);
            myGridView.AutoGenerateColumns = true;
            btnCheckdata.ServerClick += new EventHandler(btnCheckdata_ServerClick);  
           
            if (!IsPostBack)
            {
                #region 加载页面的默认视图
                if (TableType == "Receivable")
                {
                    lbTitle.Text = "应收";
                }
                else if (TableType == "Payable")
                {
                    lbTitle.Text = "应付";
                }
                else if (TableType == "Checking")
                {
                    lbTitle.Text = "项目核算";
                }

                

                bool tableExists = false;
                tableExists = bll.DbTableExists(TableName);
                if (tableExists)
                {
                    myGridView.DataSource = bll.GetTableDataByName(TableName);
                    myGridView.DataBind();

                    List<string> cols = bll.GetComparableTableColumns(TableName);
                    StringBuilder sb = new StringBuilder();
                    for (int i = 0; i < cols.Count; i++)
                    {
                        sb.Append(cols[i] + ",");
                    }
                    tbxShow.Text = sb.ToString().Trim(',');
                }
                #endregion
            }
        }

        void btnCheckdata_ServerClick(object sender, EventArgs e)
        {
            if (tbxInput.Text.Trim() != "")
            {
                Bind();
                hdfChecked.Value = "1";
                tbxInput.ReadOnly = true;
                btnSave.Disabled = false;
            }
        }
        void btnSave_ServerClick(object sender, EventArgs e)
        {
           // btnCheckdata_ServerClick(null, null);


            if (myGridView!=null
                && myGridView.Rows.Count>1)
            {
           
                if (Valid())
                {
                    //保存要比较的列
                    SaveComparableCols();
                    if (tbxInput.Text.Trim() != "")
                    {
                        //创建表格到数据库
                        CreateTable2DB();
                        //导入数据到表
                        ImportData2DB();
                        tbxInput.Text = "";//情况，防止再次解析
                        // tbxInput.Enabled = false;
                    }
                    Lib.JScript.MsgBox(Page, "保存成功！");
                }
            }
        }
        protected void myGridView_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow &&  e.Row.DataItemIndex == 0)
            {
                DataRowView drv = e.Row.DataItem as DataRowView;
                if (drv != null )
                {
                    for(int i=0;i<e.Row.Cells.Count;i++)
                    {
                        e.Row.Cells[i].Text = string.Format("<input type='checkbox' title='{0}' id='chx{1}' name='chx' value='{0}'><label for='chx{1}'>勾选</label>"
                            , drv.Row.Table.Columns[i].ColumnName, i.ToString());
                    }
                }
                
            }
        }
       
        public void Bind()
        {
            DataTable dt;
            if (!GetDataTable(out dt))
            {
                return;
            }
            if (dt == null)
            {
                return;
            }
            DataRow dr = dt.NewRow();
            dt.Rows.InsertAt(dr, 0);//加一个空行
            myGridView.DataSource = dt;
            myGridView.DataBind();
           
        }
        #region 保存前，检验数据有效性
        private bool Valid()
        {
            int hasID = 0;
            int idxIDRow = 0;
            List<int> SelectedTimeCol = new List<int>();
            string flds = "," + tbxShow.Text + ",";
            for (int i = 0; i < myGridView.HeaderRow.Cells.Count; i++)
            {
                TableCell cell = myGridView.HeaderRow.Cells[i];
                if (cell.Text.Trim() != "")
                {
                    string col = cell.Text.Trim();
                    if (col.ToUpper() == "ID")
                    {
                        hasID++;        //记下“ID”列的个数
                        idxIDRow = i;   //记下“ID”列的位置
                    }
                    if (flds.IndexOf(col) > -1)
                    {
                        SelectedTimeCol.Add(i);//记下勾选的时间列的位置
                    }
                }
                else
                {
                    { Lib.JScript.MsgBox(Page, "表头不能空"); return false; }
                }
            }
            if (hasID < 1) { Lib.JScript.MsgBox(Page, "缺少ID列"); return false; }
            if (hasID > 1) { Lib.JScript.MsgBox(Page, "ID列重复"); return false; }

            string celltxt;
            DateTime dtm;
            for (int j = 1; j < myGridView.Rows.Count; j++)
            {
                GridViewRow gvr = myGridView.Rows[j];
                 //没有ID号的行，忽略
                if (IsNullPrjID(gvr,idxIDRow))
                {
                    continue;  
                }
                for (int n = 0; n < SelectedTimeCol.Count; n++)
                {
                    celltxt = gvr.Cells[SelectedTimeCol[n]].Text.Trim().Replace("&nbsp;","");
                    if (celltxt != "") 
                    {
                        if (Common.HasChzChar(celltxt) || !DateTime.TryParse(celltxt, out dtm))
                        {
                            Lib.JScript.MsgBox(Page, myGridView.HeaderRow.Cells[SelectedTimeCol[n]].Text + "的第" + j.ToString() + "行有误,“" + celltxt + "”不符合标准的日期格式"); 
                            return false; 
                        }
                    }
                }
            }
           
            return true;
        }
#endregion
        #region 建Excel对应的物理表,并导入数据库
        #region --保存要检查的数据列
        public void SaveComparableCols()
        {
            List<string> list = new List<string>();
            string[] fields = tbxShow.Text.Split(',');
            for (int i = 0; i < fields.Length; i++)
            {
                list.Add(fields[i]);
            }
            bll.SaveComparableTableColumns(TableName, list);
        }
        #endregion
        private void ImportData2DB()
        {
            Hashtable ht = new Hashtable();
            int L = myGridView.HeaderRow.Cells.Count;
            
            TableCellCollection clt = myGridView.HeaderRow.Cells;
            //字段列表字符串
            StringBuilder fieldSB = new StringBuilder();
            //参数列表字符串
            StringBuilder paramSB = new StringBuilder();
            //ID列的位置
            int IdColIndx = 0;
            for (int i = 0; i < L; i++)
            {
                TableCell cell = clt[i];
                fieldSB.Append("[" + cell.Text.Trim() + "],");//格式如:[字段],
                paramSB.Append("@p{0}_"+i.ToString()+",");//格式如:@p{0}_1,
                if (cell.Text.Trim().ToUpper() == "ID")//找到ID列
                {
                    IdColIndx = i;
                }
            }
            string fieldStr = fieldSB.ToString().Trim(',');
            string paramStr = paramSB.ToString().Trim(',');
            
            
            SqlDbType ty = SqlDbType.NVarChar;
            string tempVal = "";
            object obj = null;
            string SelFieldStr= "," + tbxShow.Text + ",";
            for (int j = 1; j < myGridView.Rows.Count; j++)
            {               
                //没有ID号的行，忽略
                if (IsNullPrjID(myGridView.Rows[j], IdColIndx))
                {
                    continue;
                }
                SqlParameter[] parms = new SqlParameter[L];
                StringBuilder sql = new StringBuilder();
              //  StringBuilder sb = new StringBuilder(32);
                GridViewRow gvr = myGridView.Rows[j];
                for (int n = 0; n < L; n++)
                {
                    string temp = ","+clt[n].Text.Trim()+",";
                    if (SelFieldStr.IndexOf(temp) > -1)
                    {
                        tempVal = gvr.Cells[n].Text.Replace("&nbsp;", "").Trim();
                        if(string.IsNullOrEmpty(tempVal)){
                            obj = DBNull.Value;
                        }else{
                            obj = tempVal;
                        }
                        ty = SqlDbType.DateTime;//选中的列，作为DateTime类型处理
                        (parms[n] = new SqlParameter("@p" + j.ToString() + "_" + n.ToString(), ty)).Value = obj;
                    }
                    else
                    {
                        ty = SqlDbType.NVarChar;
                        (parms[n] = new SqlParameter("@p" + j.ToString() + "_" + n.ToString(), ty, 200)).Value = gvr.Cells[n].Text.Trim().Replace("&nbsp;", "");
                    }
                    
                }
                sql.Append("Insert into " + TableName + "(" + fieldStr + ")");
                sql.Append(" values(");
                sql.AppendFormat(paramStr, j);
                sql.Append(")");
                //将SQL和SqlParameter[]添加到hash表
                ht.Add(sql.ToString(), parms);
            }
            
            bll.ImportDataBySQLAndParams(TableName, ht);
        }
        private void CreateTable2DB()
        {
            //if (!Valid()) { return; }           
            string createsql = BuildCreateDBTableSQL();
            
            bll.ExecuteSQL(createsql);

        }        
        private string BuildCreateDBTableSQL()
        {
            string tableName = "dbo.f_"+ TableType;
            StringBuilder sb = new StringBuilder(128);
            sb.AppendFormat(@"
                        IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{0}') AND type in (N'U'))
                        BEGIN
	                        drop table {0}
                        END
                        CREATE TABLE {0}(",TableName);
           
            for (int i = 0; i < myGridView.HeaderRow.Cells.Count; i++)
            {
                TableCell cell = myGridView.HeaderRow.Cells[i];
                if (cell.Text.Trim() != "")
                {

                    string flds = "," + tbxShow.Text + ",";
                    string col = cell.Text.Trim() ;
                    if (col.ToUpper() == "ID")
                    {
                        sb.AppendFormat("[ID] [nvarchar](30),", col);
                    }
                    else if (flds.IndexOf(","+col+",") > -1)
                    {
                        sb.AppendFormat("[{0}] [DateTime] NULL,", col);                        
                    }
                    else
                    {
                        sb.AppendFormat("[{0}] [nvarchar](100) NULL,", col);
                    }
                   
                }
                else
                {
                    throw new Exception("表格标题栏不能为空");
                }
            }
           
//            sb.AppendFormat(@"
//	                        [UR_ID] [int] IDENTITY(1,1) NOT NULL,
//	                        [UserID] [int] NOT NULL,
//	                        [RoleID] [int] NOT NULL,");

            sb.AppendFormat(@"
                            CONSTRAINT [PK_{0}] PRIMARY KEY CLUSTERED 
                        (
	                        [ID] ASC
                        ))
                        ",TableType);
            return sb.ToString();
            
        }
        #endregion
        #region 将Excel数据，填充DataTable
        private bool  GetDataTable(out DataTable newDataTable)
        {
            string str = tbxInput.Text.TrimEnd(new char[]{'\r','\n'});
            string[] trs = Regex.Split(str, "\r\n");
            string head = trs[0];
            string[] ths = Regex.Split(head, "\t");
            //得到DataTable
            DataTable dt = new DataTable();

            if (!CreateDataTable(ths,dt))
            {
                newDataTable = null;
                return false;
            }
            //填充数据
            for (int i = 1; i < trs.Length; i++)
            {
                string tr = trs[i];
                string[] tds = Regex.Split(tr, "\t");
                DataRow dr = dt.NewRow();
                for (int j = 0; j < ths.Length; j++)
                {
                    dr[ths[j]] = tds[j];
                }
                dt.Rows.Add(dr);
            }
            newDataTable = dt;
            return true;
        }
        #endregion
        #region 拿Excel表头,创建DataTable
        private bool CreateDataTable(string[] ths,DataTable dt)
        {            
            for (int i = 0; i < ths.Length; i++)
            {
                if (ths[i].Trim() == "")
                {
                    Lib.JScript.MsgBox(Page, "标题不能为空");
                    return false;
                }
                if (Regex.Match(ths[i].Trim(), @"^[\d\#\*\?\@\!\$\^\&\(\)\+\-\`\%\r\n]").Success)
                {
                    Lib.JScript.MsgBox(Page, "标题不能有特殊符号");
                    return false;
                }
                DataColumn col = new DataColumn(ths[i], typeof(string));
                dt.Columns.Add(col);
            }
            return true;
        }
        #endregion
        private bool IsNullPrjID(GridViewRow gvr,int rowIdx)
        {
             //没有ID号的行，忽略
                return (gvr.RowType != DataControlRowType.DataRow
                    ||gvr.Cells[rowIdx].Text.Replace("&nbsp;", "").Trim() == ""
                    ||gvr.Cells[rowIdx].Text.Replace("&nbsp;", "").Trim() == "0"
                    );
        }
    }
}